﻿Imports System.Data.SqlClient

Partial Class Browse_Default
    Inherits System.Web.UI.Page
    Public Sub BindParentDataList()
        Dim connectionString As String = "Data Source=MGUZZI-PC;Initial Catalog=PeDALS_PROD;User Id=pedpublic;Password=pa$$w0rd"

        Dim mySqlConnection As New SqlConnection(connectionString)

        Dim mySqlCommand As New SqlCommand("SELECT  ProvenanceId, ProvenanceName AS Name FROM Provenance UNION SELECT Provenance.ProvenanceId, ProvenanceVariant.ProvenanceVariantName AS Name FROM Provenance INNER JOIN ProvenanceVariant ON Provenance.ProvenanceId = ProvenanceVariant.ProvenanceId INNER JOIN Qualifier ON dbo.ProvenanceVariant.QualifierId = dbo.Qualifier.QualifierId WHERE (Qualifier.QualifierType = 'KEYFORM') ORDER BY Name", mySqlConnection)

        If mySqlConnection.State = Data.ConnectionState.Closed Then
            mySqlConnection.Open()
        End If

        Dim myDataReader As SqlDataReader = mySqlCommand.ExecuteReader()

        parentDataList.DataSource = myDataReader
        parentDataList.DataBind()

        myDataReader.Close()

        If mySqlConnection.State = Data.ConnectionState.Open Then
            mySqlConnection.Close()
        End If

        For Each Item As DataListItem In parentDataList.Items
            BindNestedDataList(Item.ItemIndex)
        Next
    End Sub
    Public Sub BindNestedDataList(ByVal ItemIndex As Integer)
        Dim ProvenanceId As Integer = parentDataList.DataKeys(ItemIndex)

        Dim connectionString As String = "Data Source=aslaprpedsql;Initial Catalog=pedals_dev;User Id=pedals;Password=pedals"

        Dim mySqlConnection As New SqlConnection(connectionString)

        Dim mySqlCommand As New SqlCommand("SELECT SeriesTitle FROM Series WHERE (ProvenanceId = @ProvenanceId) ORDER BY SeriesTitle", mySqlConnection)

        mySqlCommand.Parameters.Add("@ProvenanceId", Data.SqlDbType.Int).Value = ProvenanceId

        If mySqlConnection.State = Data.ConnectionState.Closed Then
            mySqlConnection.Open()
        End If

        Dim myDataReader As SqlDataReader = mySqlCommand.ExecuteReader()

        Dim nestedDataList As DataList = DirectCast(parentDataList.Items(ItemIndex).FindControl("nestedDataList"), DataList)

        nestedDataList.DataSource = myDataReader
        nestedDataList.DataBind()

        myDataReader.Close()

        If mySqlConnection.State = Data.ConnectionState.Open Then
            mySqlConnection.Close()
        End If
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        BindParentDataList()
    End Sub
End Class

